﻿CREATE VIEW [dbo].[V_UDF_MATERIAL] AS SELECT DISTINCT A.FNUMBER SKUCODE,B.FNAME SKUNAME,ISNULL(A.FMODIFYDATE,A.FCREATEDATE)UPDATETIME,
'C' SKUBAND,'1' PRODUCTCODE,'' COLOUR,'' REMARK ,'' UNIT
FROM T_BD_MATERIAL A LEFT JOIN T_BD_MATERIAL_L B ON A.FMATERIALID = B.FMATERIALID WHERE B.FLOCALEID= 2052 AND A.FCREATEORGID = A.FUSEORGID
GO



CREATE VIEW [dbo].[V_UDF_FINDSTOCKOUT4WMS] AS SELECT  A.FBILLNO,D.FREALQTY,'' ADDRESS,C.FNAME CUSTOMERNAME,E.FNUMBER,F.FNAME,1 TYPE FROM T_SAL_OUTSTOCK A  
LEFT JOIN T_BD_CUSTOMER B ON A.FCUSTOMERID = B.FCUSTID
LEFT JOIN T_BD_CUSTOMER_L C ON B.FCUSTID = C.FCUSTID AND C.FLOCALEID= 2052
LEFT JOIN T_SAL_OUTSTOCKENTRY D ON D.FID = A.FID
LEFT JOIN T_BD_MATERIAL E ON E.FMATERIALID = D.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L F ON F.FMATERIALID = E.FMATERIALID AND F.FLOCALEID= 2052
WHERE D.FSTOCKID = 116648 AND D.F_UIVG_TEXT_RE5  = '' --NOT EXISTS(SELECT 1 FROM UDF_STOCKOUT_RECORD X WHERE X.BILLCODE = A.FBILLNO)

UNION ALL

SELECT  A.FBILLNO,D.FQTY,'' ADDRESS,'' CUSTOMERNAME,E.FNUMBER,F.FNAME ,2 TYPE
FROM T_STK_MISDELIVERY A  
LEFT JOIN T_STK_MISDELIVERYENTRY D ON D.FID = A.FID
LEFT JOIN T_BD_MATERIAL E ON E.FMATERIALID = D.FMATERIALID
LEFT JOIN T_BD_MATERIAL_L F ON F.FMATERIALID = E.FMATERIALID AND F.FLOCALEID= 2052
WHERE D.FSTOCKID = 116648 AND D.F_UIVG_TEXT_QTR = ''  -- NOT EXISTS(SELECT 1 FROM UDF_STOCKOUT_RECORD X WHERE X.BILLCODE = A.FBILLNO)


UNION ALL
 
SELECT  A.FBILLNO,D.FSTOCKACTUALQTY,'' ADDRESS,'' CUSTOMERNAME,E.FNUMBER,F.FNAME ,3 TYPE 
  FROM T_PRD_PICKMTRL A  
  LEFT JOIN T_PRD_PICKMTRLDATA D ON D.FID = A.FID
  LEFT JOIN T_BD_MATERIAL E ON E.FMATERIALID = D.FMATERIALID
  LEFT JOIN T_BD_MATERIAL_L F ON F.FMATERIALID = E.FMATERIALID AND F.FLOCALEID= 2052
 WHERE D.FSTOCKID = 116648 AND D.F_UIVG_TEXT_APV = ''  -- NOT EXISTS(SELECT 1 FROM UDF_STOCKOUT_RECORD X WHERE X.BILLCODE = A.FBILLNO)

UNION ALL

SELECT 
   T1.FBILLNO,F2.FQTY,'','',M.FNUMBER,L.FNAME,7 
FROM
	T_STK_STKTRANSFERIN T1	
	LEFT JOIN T_STK_STKTRANSFERINENTRY T2 ON T1.FID = T2.FID
	LEFT JOIN T_BD_MATERIAL M ON M.FMATERIALID=T2.FMATERIALID
	LEFT JOIN T_BD_MATERIAL_L L ON L.FMATERIALID=M.FMATERIALID
	WHERE T2.FDESTSTOCKID = 116648 AND T2.F_TBKE_YHRBS = ''

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'提供给WMS系统出库的' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_UDF_FINDSTOCKOUT4WMS'
GO



CREATE VIEW [dbo].[V_UDF_FINDSTOCK4WMS] AS SELECT A.FENTRYID,A.FSEQ,B.FBILLNO,B.FDATE,FSRCBILLNO,FREALQTY,C.FNUMBER,D.FNAME,/*B2.FNUMBER*/A.F_UNW_HRXH STOCKLOC,4 TYPE
 FROM T_PRD_INSTOCKENTRY A 
 LEFT JOIN T_PRD_INSTOCK B ON A.FID = B.FID 
 LEFT JOIN T_BD_MATERIAL C ON C.FMATERIALID = A.FMATERIALID 
 LEFT JOIN T_BD_MATERIAL_L D ON D.FMATERIALID = A.FMATERIALID AND D.FLOCALEID= 2052
 --LEFT JOIN T_BAS_FLEXVALUESDETAIL B1 ON B1.FID = A.FSTOCKLOCID
 --LEFT JOIN T_BAS_FLEXVALUESENTRY B2 ON B2.FENTRYID = B1.FF100002
 WHERE A.FSTOCKID = 116648 AND A.F_UIVG_TEXT_TZK = '' -- NOT EXISTS (SELECT 1 FROM UDF_WMS_STORKIN_RECORD WHERE RECORDID = A.FENTRYID AND FBILLNO = B.FBILLNO)
 
 UNION ALL
 
SELECT A.FENTRYID,A.FSEQ,B.FBILLNO,B.FDATE,B.FBILLNO,A.FQTY,C.FNUMBER,D.FNAME,/*B2.FNUMBER*/A.F_UNW_HRXH STOCKLOC,5 TYPE
  FROM T_STK_MISCELLANEOUSENTRY A 
  LEFT JOIN T_STK_MISCELLANEOUS B ON A.FID=B.FID
  LEFT JOIN T_BD_MATERIAL C ON C.FMATERIALID = A.FMATERIALID 
  LEFT JOIN T_BD_MATERIAL_L D ON D.FMATERIALID = A.FMATERIALID AND D.FLOCALEID= 2052
  --LEFT JOIN T_BAS_FLEXVALUESDETAIL B1 ON B1.FID = A.FSTOCKLOCID
  --LEFT JOIN T_BAS_FLEXVALUESENTRY B2 ON B2.FENTRYID = B1.FF100002
 WHERE A.FSTOCKID = 116648 AND A.F_UIVG_TEXT_83G = ''  -- NOT EXISTS (SELECT 1 FROM UDF_WMS_STORKIN_RECORD WHERE RECORDID = A.FENTRYID AND BILLNO = B.FBILLNO)
  
 UNION ALL
 SELECT 
  T2.FENTRYID,T2.FSEQ, T1.FBILLNO,T1.FDATE,T1.FBILLNO,F2.FQTY,M.FNUMBER,L.FNAME,T2.F_UNW_HRXH,6 TYPE
FROM
	T_STK_STKTRANSFERIN T1	
	LEFT JOIN T_STK_STKTRANSFERINENTRY T2 ON T1.FID = T2.FID
	LEFT JOIN T_BD_MATERIAL M ON M.FMATERIALID=T2.FMATERIALID
	LEFT JOIN T_BD_MATERIAL_L L ON L.FMATERIALID=M.FMATERIALID
	WHERE T2.FDESTSTOCKID = 116648 AND T2.F_TBKE_YHRBS = ''

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'该视图用于WMS的入库单取数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_UDF_FINDSTOCK4WMS'
GO